package com.easy.util;

import java.beans.BeanInfo;
import java.beans.IntrospectionException;
import java.beans.Introspector;
import java.beans.PropertyDescriptor;
import java.io.InputStream;
import java.lang.reflect.InvocationTargetException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

import javax.sql.DataSource;

import org.apache.commons.dbcp.BasicDataSourceFactory;

public class JDBC {
	
	public static void init() {
		
	}
	
	private static DataSource ds = null;
    //在静态代码块中创建数据库连接池
    static{
        try{
            //加载dbcpconfig.properties配置文件
            InputStream in = JDBC.class.getClassLoader().getResourceAsStream("dbcpconfig.properties");
            Properties prop = new Properties();
            prop.load(in);
            //创建数据源
            ds = BasicDataSourceFactory.createDataSource(prop);
        }catch (Exception e) {
        	e.printStackTrace();
            throw new ExceptionInInitializerError(e);
        }
    }

    /**
    * @Method: getConnection
    * @Description: 从数据源中获取数据库连接
    * @return Connection
    * @throws SQLException
    */
    public static Connection getConnection() throws SQLException{
        //从数据源中获取数据库连接
        return ds.getConnection();
    }

    //约束--属性和列名要一样  如果不一样使用as  必须有无参构造方法
    /**
     * 查询指定类型的一个数据
     * @param <T>
     * @param sql
     * @param clazz
     * @return
     * @throws SQLException
     * @throws InstantiationException
     * @throws IllegalAccessException
     * @throws IntrospectionException
     * @throws IllegalArgumentException
     * @throws InvocationTargetException
     */
    public static <T> T query(String sql,Class<T> clazz) throws SQLException, InstantiationException, IllegalAccessException, IntrospectionException, IllegalArgumentException, InvocationTargetException {
    	Connection con=getConnection();
    	Statement sta=con.createStatement();
    	ResultSet rs=sta.executeQuery(sql);
    	T t=null;
    	BeanInfo bi=Introspector.getBeanInfo(clazz);
    	PropertyDescriptor[] p_arr=bi.getPropertyDescriptors();
    	if(rs.next()) {
    		t=clazz.newInstance();
    		for(PropertyDescriptor p:p_arr) {
    			//有可能出现异常--类型转化
    			String p_name=p.getName();
    			try {
	    			if(!p_name.equals("class")) {
		    			Object p_data=rs.getObject(p_name);
		    			p.getWriteMethod().invoke(t, p_data);
	    			}
				
    			} catch (Exception e) {
    				continue;
    			}
    		}
    	}
    	release(con,sta,rs);
    	return t;
    }
    
    //属性名和列名要一样
    //如果不一样,用as起别名
    //必须有无参构造方法
    public static <T> List<T> queryList(String sql,Class<T> clazz) throws SQLException, InstantiationException, IllegalAccessException, IntrospectionException, IllegalArgumentException, InvocationTargetException {
    	Connection con=getConnection();
    	Statement sta=con.createStatement();
    	ResultSet rs=sta.executeQuery(sql);
    	T t=null;
    	BeanInfo bi=Introspector.getBeanInfo(clazz);
    	PropertyDescriptor[] p_arr=bi.getPropertyDescriptors();
    	List<T> list=new ArrayList<T>();
    	
    	while(rs.next()) {
    		t=clazz.newInstance();
    		for(PropertyDescriptor p:p_arr) {
    			//有可能出现异常,用Object转换
	    		//比如说,我有可能查User,可能查Student
	    		//这里用的是内省的方式
    			String p_name=p.getName();
    			if(!p_name.equals("class")) {
	    			Object p_data=rs.getObject(p_name);
	    			p.getWriteMethod().invoke(t, p_data);
    			}
    		}
    		list.add(t);
    	}
    	release(con,sta,rs);
    	return list;
    }
    
    public static int queryCount(String sql) throws SQLException {
    	Connection con=getConnection();
    	Statement sta=con.createStatement();
    	ResultSet rs=sta.executeQuery(sql);
    	if(rs.next()) {
    		return rs.getInt(1);
    	}
    	return 0;
    }
    
    public static int update(String sql) throws SQLException {
    	Connection con=getConnection();
    	Statement sta=con.createStatement();
    	int result=sta.executeUpdate(sql);
    	release(con,sta,null);
    	return result;
    }
    
    public static int insert(String sql) throws SQLException {
    	Connection con=getConnection();
    	Statement sta=con.createStatement();
    	int result=sta.executeUpdate(sql);
    	release(con,sta,null);
    	return result;
    }
    
    public static List<Integer> insertlist(String sql) throws SQLException {
    	Connection con=getConnection();
//    	Statement sta=con.createStatement();
    	PreparedStatement  sta=con.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
    	long result=sta.executeUpdate();
    	System.out.println(result);
    	ResultSet rs=sta.getGeneratedKeys();
    	List<Integer> list=new ArrayList<Integer>();
    	while(rs.next()) {
    		list.add(rs.getInt(1));
    	}
    	release(con,sta,null);
    	return list;
    }
    
    public static List<Integer> insertlist(Connection con,String sql) throws SQLException {
    	PreparedStatement  sta=con.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
    	long result=sta.executeUpdate();
    	System.out.println(result);
    	ResultSet rs=sta.getGeneratedKeys();
    	List<Integer> list=new ArrayList<Integer>();
    	while(rs.next()) {
    		list.add(rs.getInt(1));
    	}
    	release(null,sta,rs);
    	return list;
    }
    
    public static int delete(String sql) throws SQLException {
    	Connection con=getConnection();
    	Statement sta=con.createStatement();
    	int result=sta.executeUpdate(sql);
    	release(con,sta,null);
    	return result;
    }
    
    /**
    * @Method: release
    * @Description: 释放资源，
    * 释放的资源包括Connection数据库连接对象，负责执行SQL命令的Statement对象，存储查询结果的ResultSet对象
    * @param conn
    * @param st
    * @param rs
    */
    public static void release(Connection conn,Statement st,ResultSet rs){
        if(rs!=null){
            try{
                //关闭存储查询结果的ResultSet对象
                rs.close();
            }catch (Exception e) {
                e.printStackTrace();
            }
            rs = null;
        }
        if(st!=null){
            try{
                //关闭负责执行SQL命令的Statement对象
                st.close();
            }catch (Exception e) {
                e.printStackTrace();
            }
        }

        if(conn!=null){
            try{
                //将Connection连接对象还给数据库连接池
                conn.close();
            }catch (Exception e) {
                e.printStackTrace();
            }
        }
    }
}